from typing import List, Optional, Union, Dict, Any

from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.crud.base import CRUDBase
from app.models.contacts import Contact
from app.models.messages import Message
from app.schemas.contacts import ContactCreate, ContactUpdate


class CRUDContact(CRUDBase[Contact, ContactCreate, ContactUpdate]):
    async def get_by_wechat_id(self, db: AsyncSession, *, wechat_id: str) -> Optional[Contact]:
        """
        根据微信ID获取联系人
        """
        result = await db.execute(select(Contact).filter(Contact.wechat_id == wechat_id))
        return result.scalars().first()
    
    async def get_with_message_stats(self, db: AsyncSession, *, contact_id: int) -> Optional[Dict[str, Any]]:
        """
        获取联系人以及消息统计数据
        """
        # 获取联系人信息
        contact = await self.get(db, id=contact_id)
        if not contact:
            return None
        
        # 查询消息总数
        msg_count_query = select(func.count(Message.id)).where(Message.contact_id == contact_id)
        result = await db.execute(msg_count_query)
        message_count = result.scalar_one()
        
        # 查询最后一条消息时间
        last_msg_query = select(Message.sent_at).where(
            Message.contact_id == contact_id
        ).order_by(Message.sent_at.desc()).limit(1)
        result = await db.execute(last_msg_query)
        last_message_at = result.scalar_one_or_none()
        
        # 查询PUA警报数量
        pua_count_query = select(func.count(Message.id)).where(
            Message.contact_id == contact_id,
            Message.pua_score > 0.5
        )
        result = await db.execute(pua_count_query)
        pua_alert_count = result.scalar_one()
        
        # 构建结果
        contact_dict = {
            "id": contact.id,
            "wechat_id": contact.wechat_id,
            "nickname": contact.nickname,
            "remark": contact.remark,
            "relationship_type": contact.relationship_type,
            "description": contact.description,
            "first_interaction": contact.first_interaction,
            "last_interaction": contact.last_interaction,
            "message_count": message_count,
            "last_message_at": last_message_at,
            "pua_alert_count": pua_alert_count
        }
        
        return contact_dict
    
    async def search_contacts(
        self, 
        db: AsyncSession, 
        *, 
        keyword: Optional[str] = None,
        relationship_type: Optional[str] = None,
        skip: int = 0, 
        limit: int = 100
    ) -> List[Contact]:
        """
        搜索联系人
        """
        query = select(Contact)
        
        if keyword:
            query = query.filter(
                (Contact.nickname.contains(keyword)) | 
                (Contact.remark.contains(keyword)) |
                (Contact.wechat_id.contains(keyword))
            )
        
        if relationship_type:
            query = query.filter(Contact.relationship_type == relationship_type)
        
        query = query.offset(skip).limit(limit)
        result = await db.execute(query)
        return result.scalars().all()


contact = CRUDContact(Contact)